package org.fastsyncer.connector.framework;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang.StringUtils;
import org.fastsyncer.common.constant.ConnectorConstant;
import org.fastsyncer.common.entity.BaseInfo;
import org.fastsyncer.common.entity.DatabaseConfig;
import org.fastsyncer.connector.util.DatabaseUtil;
import org.fastsyncer.connector.util.JDBCUtil;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.jdbc.core.JdbcTemplate;

public abstract class AbstractDatabaseConnector implements Database {

    private static final Logger logger = LoggerFactory.getLogger(AbstractDatabaseConnector.class);

    @Override
    public boolean isAlive(Object config) {
        DatabaseConfig cfg = (DatabaseConfig) config;
        Connection connection = null;
        try {
            connection = JDBCUtil.getConnection(cfg.getDriverClassName(), cfg.getUrl(), cfg.getUsername(), cfg.getPassword());
            if (null != connection) {
                return true;
            }
        } catch (Exception e) {
            logger.error("Failed to connect:" + cfg.getUrl(), e.getLocalizedMessage());
        } finally {
            JDBCUtil.close(connection);
        }
        return false;
    }

    @Override
    public BaseInfo getBaseInfoConnector(Object config) {
        DatabaseConfig cfg = (DatabaseConfig) config;
        return getBaseInfoConnector(cfg, "");
    }

    @Override
    public JdbcTemplate getJdbcTemplate(DatabaseConfig config) {
        return DatabaseUtil.getJdbcTemplate(config);
    }

    @Override
    public void close(JdbcTemplate jdbcTemplate) {
        DatabaseUtil.close(jdbcTemplate);
    }
    
    /**
     * 根据过滤条件获取查询SQL
     * 
     * @param operator
     * @param list
     * @return
     */
    private String getOperSQL(String operator, List<Map<String, String>> list) {
        if (list == null || list.isEmpty()) {
            return null;
        }
        int size = list.size();
        int end = size - 1;
        StringBuilder sql = new StringBuilder();
        sql.append("(");
        Map<String, String> c;
        String name;
        String value;
        String oper;
        for (int i = 0; i < size; i++) {
            c = list.get(i);
            name = c.get("name");
            value = c.get("value");
            oper = ConnectorConstant.OPERS.get(c.get("operator"));
            // USER = 'zhangsan'
            sql.append(name).append(oper).append("'").append(value).append("'");
            if (i < end) {
                // AND
                sql.append(" ").append(operator).append(" ");
            }
        }
        sql.append(")");
        return sql.toString();
    }

    @Override
    public String getFilter(Map<String, List<Map<String, String>>> query) {
        if (query == null || query.isEmpty()) {
            return "";
        }
        // 过滤条件SQL
        StringBuilder filter = new StringBuilder();

        StringBuilder condition = new StringBuilder();
        // 拼接并且SQL
        List<Map<String, String>> addList = query.get(ConnectorConstant.OPERTION_QUERY_AND);
        String addSql = this.getOperSQL(ConnectorConstant.OPERTION_QUERY_AND, addList);
        // 如果Add条件存在
        if (StringUtils.isNotBlank(addSql)) {
            condition.append(addSql);
        }

        // 拼接或者SQL
        List<Map<String, String>> orList = query.get(ConnectorConstant.OPERTION_QUERY_OR);
        String orSql = this.getOperSQL(ConnectorConstant.OPERTION_QUERY_OR, orList);
        // 如果Or条件和Add条件都存在
        if (StringUtils.isNotBlank(orSql) && StringUtils.isNotBlank(addSql)) {
            condition.append(" OR ").append(orSql);
        }

        // 如果有条件加上 WHERE
        if (StringUtils.isNotBlank(condition.toString())) {
            // WHERE (USER.USERNAME = 'zhangsan' AND USER.AGE='20') OR (USER.TEL='18228035202')
            filter.insert(0, " WHERE ").append(condition);
        }
        return filter.toString();
    }

    @Override
    public String getJdbcSQL(String opertion, DatabaseConfig config, List<Map<String, Object>> fileds, String queryFilter) {
        // 去掉重复的查询字段
        List<String> filedNames = new ArrayList<String>();
        for (Map<String, Object> obj : fileds) {
            String name = (String) obj.get("name");
            if (StringUtils.isBlank(name)) {
                continue;
            }
            // 如果没有重复
            if (!filedNames.contains(name)) {
                filedNames.add(name);
            }
        }
        if (filedNames.isEmpty()) {
            return "";
        }
        int size = filedNames.size();
        int end = size - 1;
        String table = config.getTable().toUpperCase();
        StringBuilder sql = new StringBuilder();
        switch (opertion) {
        case ConnectorConstant.OPERTION_QUERY:
            for (int i = 0; i < size; i++) {
                // USER.USERNAME
                sql.append(table).append(".").append(filedNames.get(i));
                //如果不是最后一个字段
                if (i < end) {
                    sql.append(", ");
                }
            }
            // SELECT USER.ID,USER.NAME FROM USER
            sql.insert(0, "SELECT ").append(" FROM ").append(table);
            // 解析查询条件
            if (StringUtils.isNotBlank(queryFilter)) {
                sql.append(queryFilter);
            }
            // 分页语句
            sql = getPageSQL(config, sql);
            break;
        case ConnectorConstant.OPERTION_QUERY_QUARTZ:
            for (int i = 0; i < size; i++) {
                // USER.USERNAME
                sql.append(table).append(".").append(filedNames.get(i));
                //如果不是最后一个字段
                if (i < end) {
                    sql.append(", ");
                }
            }
            // SELECT USER.ID,USER.NAME FROM USER
            sql.insert(0, "SELECT ").append(" FROM ").append(table);
            // 解析查询条件
            if (StringUtils.isNotBlank(queryFilter)) {
                sql.append(queryFilter);
            }
            break;
        case ConnectorConstant.OPERTION_INSERT:
            StringBuilder fs = new StringBuilder();
            StringBuilder vs = new StringBuilder();
            for (int i = 0; i < size; i++) {
                // USER.USERNAME
                fs.append(table).append(".").append(filedNames.get(i));
                vs.append("?");
                //如果不是最后一个字段
                if (i < end) {
                    fs.append(", ");
                    vs.append(", ");
                }
            }
            // INSERT INTO USER(USER.USERNAME,USER.AGE) VALUES (?,?)
            sql.insert(0, "INSERT INTO ").append(table).append("(").append(fs).append(") VALUES (").append(vs).append(")");
            break;
        case ConnectorConstant.OPERTION_UPDATE:
            // UPDATE USER SET 
            sql.append("UPDATE ").append(table).append(" SET ");
            for (int i = 0; i < size; i++) {
                // USER.USERNAME=?
                sql.append(table).append(".").append(filedNames.get(i)).append("=?");
                //如果不是最后一个字段
                if (i < end) {
                    sql.append(",");
                }
            }
            // UPDATE USER SET USER.USERNAME=?,USER.AGE=?, WHERE USER.ID=?
            sql.append(" WHERE ").append(table).append(".").append(config.getPrimaryKey().toUpperCase()).append("=?");
            break;
        case ConnectorConstant.OPERTION_DELETE:
            // DELETE FROM USER WHERE USER.ID=?
            sql.append("DELETE FROM ").append(table).append(" WHERE ").append(table).append(".").append(config.getPrimaryKey().toUpperCase()).append("=?");
            break;
        default:
            break;
        }
        return sql.toString();
    }

    @Override
    public String getJdbcQuartzRangeSQL(String table, String quartzFiled, String queryDatabaseFilter) {
        quartzFiled = table + "." + quartzFiled;
        StringBuilder f = new StringBuilder();
        // 如果没有加过滤条件就拼接WHERE语法
        f.append(StringUtils.isBlank(queryDatabaseFilter) ? " WHERE " : " AND ");
        // TB_USER.LASTDATE > ? AND TB_USER.LASTDATE <= ?
        f.append(quartzFiled).append(" > ?").append(" AND ").append(quartzFiled).append(" <= ?").append(" ORDER BY ").append(quartzFiled);
        // LASTDATE > '2017-11-10 11:07:41' AND LASTDATE <= '2017-11-10 11:30:01'
        return f.toString();
    }

    @Override
    public String getJdbcQuartzAllSQL(String table, String quartzFiled, String queryDatabaseFilter) {
        StringBuilder f = new StringBuilder();
        // 如果没有加过滤条件就拼接WHERE语法
        f.append(StringUtils.isBlank(queryDatabaseFilter) ? " WHERE " : " AND ");
        // TB_USER.LASTDATE <= ?
        f.append(table).append(".").append(quartzFiled).append(" <= ?").append(" ORDER BY ").append(table).append(".").append(quartzFiled);
        // TB_USER.LASTDATE <= '2017-11-10 11:07:41'
        return f.toString();
    }

    @Override
    public String getJdbcQuartzMaxSQL(String table, String quartzFiled) {
        StringBuilder f = new StringBuilder();
        // SELECT MAX(USER.LASTDATE) FROM TB_USER
        f.append("SELECT MAX(").append(table).append(".").append(quartzFiled).append(") FROM ").append(table);
        return f.toString();
    }

    @Override
    public void batchRowsSetter(PreparedStatement ps, List<Map<String, Object>> fileds, Map<String, Object> row) {
        int filedSize = fileds.size();
        Map<String, Object> filed;
        int type;
        Object val;
        for (int i = 0; i < filedSize; i++) {
            // 取出字段和对应值
            filed = fileds.get(i);
            type = Integer.parseInt(filed.get("type").toString());
            val = row.get(filed.get("name"));
            DatabaseUtil.preparedStatementSetter(ps, i + 1, type, val);
        }
    }

}
